/**
 * 
 */
package org.gov.vis.data.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.gov.vis.data.dto.ProcurementLNDto;
import org.gov.vis.util.DBConstants;
import org.gov.vis.util.DBStatements;
import org.gov.vis.util.DU;
import org.gov.vis.util.Util;

/**
 * @author user
 *
 */
public class ProcurementLnDaoImpl implements ProcurementLnDao{

	/**
	 *Method called to either Add or Edit a ProcurementLn
	 */
	@Override
	public void addOrEditProcurementLn(final ProcurementLNDto _procurementLnDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
			conn = Util.getConnection();
			if(_procurementLnDto.getPrLnId() > 0){
				pStmt = conn.prepareStatement(DBStatements.EDIT_LN);
				pStmt.setString(1, _procurementLnDto.getCode());
				pStmt.setString(2, DU.format(_procurementLnDto.getDoRpt(), DBConstants.MYSQL_DATE_FORMAT));
				pStmt.setDouble(3, Double.valueOf(_procurementLnDto.getKgs()));
				pStmt.setDouble(4, Double.valueOf(_procurementLnDto.getCnvrtLtrs()));
				pStmt.setString(5, _procurementLnDto.getFrmStation());
				pStmt.setString(6, _procurementLnDto.getVehicleNo());
				pStmt.setString(7, _procurementLnDto.getEvoprLoss());
				pStmt.setLong(8,_procurementLnDto.getPrLnId());
			}
			else{
				pStmt = conn.prepareStatement(DBStatements.ADD_LN);
				pStmt.setString(1, _procurementLnDto.getCode());
				pStmt.setString(2, DU.format(_procurementLnDto.getDoRpt(), DBConstants.MYSQL_DATE_FORMAT));
				pStmt.setDouble(3, Double.valueOf(_procurementLnDto.getKgs()));
				pStmt.setDouble(4, Double.valueOf(_procurementLnDto.getCnvrtLtrs()));
				pStmt.setString(5, _procurementLnDto.getFrmStation());
				pStmt.setString(6, _procurementLnDto.getVehicleNo());
				pStmt.setString(7, _procurementLnDto.getEvoprLoss());
				
			}

			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}
	}//end addOrEditDept

	/**
	 *Method called to Delete a ProcurementLn
	 */
	@Override
	public void deleteProcurementLn(final long _procurementLnId) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.DELETE_LN);
			pStmt.setLong(1, _procurementLnId);
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

	}//end deleteDept

	/**
	 *Method called to get the list of ProcurementLns based on the passed ProcurementLn 
	 */
	@Override
	public List<ProcurementLNDto> getProcurementLns(final ProcurementLNDto _procurementLnDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<ProcurementLNDto> _procurementLnList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_LN);
			if(_procurementLnDto.getPrLnId() > 0){
				pStmt.setLong(1, _procurementLnDto.getPrLnId());
				pStmt.setLong(2, _procurementLnDto.getPrLnId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}
			if(_procurementLnDto.getCode() != null && !_procurementLnDto.getCode().trim().equals("")){
				pStmt.setString(3, _procurementLnDto.getCode());
			}
			else{
				pStmt.setString(3, "%");
			}		
			if(_procurementLnDto.getFrmStation() != null && !_procurementLnDto.getFrmStation().trim().equals("")){
				pStmt.setString(4, _procurementLnDto.getFrmStation());
			}
			else{
				pStmt.setString(4, "%");
			}
			if(_procurementLnDto.getVehicleNo() != null && !_procurementLnDto.getVehicleNo().trim().equals("")){
				pStmt.setString(5, _procurementLnDto.getVehicleNo());
			}
			else{
				pStmt.setString(5, "%");
			}
			rsSet = pStmt.executeQuery();
			
			_procurementLnList = populateProcurementLns(rsSet);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _procurementLnList;
	}//end getProcurementLns


	/**
	 * Method called to populate the ProcurementLn records based on the passed result set
	 * @param _rsSet
	 * @return
	 */
	private List<ProcurementLNDto> populateProcurementLns(final ResultSet _rsSet)throws Exception{
		List<ProcurementLNDto> _procurementLnList = null;
		ProcurementLNDto _procurementLnDto = null;
		
		while(_rsSet.next()){
			if(_rsSet.isFirst()){
				_procurementLnList = new ArrayList<ProcurementLNDto>();
			}
			//initialize the ProcurementLn
			_procurementLnDto = new ProcurementLNDto();
			_procurementLnDto.setCode(_rsSet.getString(DBConstants.COL_PROCUREMENT_LN_CODE));
			_procurementLnDto.setCnvrtLtrs(_rsSet.getString(DBConstants.COL_PROCUREMENT_LN_CNVRTDLTRS));
			_procurementLnDto.setPrLnId(_rsSet.getLong(DBConstants.COL_PROCUREMENT_LN_ID));
			_procurementLnDto.setDoRpt(_rsSet.getDate(DBConstants.COL_PROCUREMENT_LN_DORCPT));
			_procurementLnDto.setEvoprLoss(_rsSet.getString(DBConstants.COL_PROCUREMENT_LN_EVOPRLOSS));
			_procurementLnDto.setFrmStation(_rsSet.getString(DBConstants.COL_PROCUREMENT_LN_FRMSTATION));
			_procurementLnDto.setKgs(_rsSet.getString(DBConstants.COL_PROCUREMENT_LN_KGS));
			_procurementLnDto.setVehicleNo(_rsSet.getString(DBConstants.COL_PROCUREMENT_LN_VEHICLENO));
			
			//add the current Object to the list
			_procurementLnList.add(_procurementLnDto);
			
		}
		return _procurementLnList;
	}//end populateProcurementLns

	/**
	 * Method is called to search the ProcurementLns based on the procurementLn.
	 */
	@Override
	public List<ProcurementLNDto> searchProcurementLns(final ProcurementLNDto _procurementLnDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<ProcurementLNDto> _procurementLnList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_LN);
			if(_procurementLnDto.getPrLnId() > 0){
				pStmt.setLong(1, _procurementLnDto.getPrLnId());
				pStmt.setLong(2, _procurementLnDto.getPrLnId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}
			if(_procurementLnDto.getCode() != null && !_procurementLnDto.getCode().trim().equals("")){
				pStmt.setString(3, _procurementLnDto.getCode()+ "%" );
			}
			else{
				pStmt.setString(3, "%");
			}
			if(_procurementLnDto.getFrmStation() != null && !_procurementLnDto.getFrmStation().trim().equals("")){
				pStmt.setString(4, _procurementLnDto.getFrmStation() + "%");
			}
			else{
				pStmt.setString(4, "%");
			}
			if(_procurementLnDto.getVehicleNo() != null && !_procurementLnDto.getVehicleNo().trim().equals("")){
				pStmt.setString(5, _procurementLnDto.getVehicleNo()+ "%" );
			}
			else{
				pStmt.setString(5, "%");
			}
			
			rsSet = pStmt.executeQuery();
			
			_procurementLnList = populateProcurementLns(rsSet);
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _procurementLnList;
	}//end searchProcurementLns

}
